﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class BolDA : IBolDA
    {
        private string BOL_ID = "@BOL_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DEPART_DATE = "@DEPART_DATE";
        private string DRIVER_ID = "@DRIVER_ID";
        private string FREIGHT_DEALER = "@FREIGHT_DEALER";
        private string FREIGHT_SELF = "@FREIGHT_SELF";
        private string GROSS_WEIGHT = "@GROSS_WEIGHT";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string NET_WEIGHT = "@NET_WEIGHT";
        private string PAID = "@PAID";
        private string QTY = "@QTY";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_BOL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@BOL WHERE BOL_ID = @BOL_ID ";
        private string SQL_DELETE_BOL = " DELETE FROM @_@BOL WHERE BOL_ID = @BOL_ID ";
        private string SQL_INSERT_BOL = " INSERT INTO @_@BOL ( WH_ID, BOL_ID, TRANSPORT_EQUIPMENT_ID, DRIVER_ID, STATUS, GROSS_WEIGHT, NET_WEIGHT, CUBE, FREIGHT_SELF, FREIGHT_DEALER, PAID, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, QTY) VALUES ( @WH_ID, @BOL_ID, @TRANSPORT_EQUIPMENT_ID, @DRIVER_ID, @STATUS, @GROSS_WEIGHT, @NET_WEIGHT, @CUBE, @FREIGHT_SELF, @FREIGHT_DEALER, @PAID, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @QTY )  ";
        private string SQL_SELECT_ALL_BOL = " SELECT  ROWID, WH_ID, BOL_ID, TRANSPORT_EQUIPMENT_ID, DRIVER_ID, STATUS, GROSS_WEIGHT, NET_WEIGHT, CUBE, FREIGHT_SELF, FREIGHT_DEALER, PAID, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, QTY, DEPART_DATE FROM @_@BOL ";
        private string SQL_UPDATE_BOL = " UPDATE @_@BOL SET WH_ID = @WH_ID, TRANSPORT_EQUIPMENT_ID = @TRANSPORT_EQUIPMENT_ID, DRIVER_ID = @DRIVER_ID, STATUS = @STATUS, GROSS_WEIGHT = @GROSS_WEIGHT, NET_WEIGHT = @NET_WEIGHT, CUBE = @CUBE, FREIGHT_SELF = @FREIGHT_SELF, FREIGHT_DEALER = @FREIGHT_DEALER, PAID = @PAID, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), QTY = @QTY, DEPART_DATE = @DEPART_DATE WHERE BOL_ID = @BOL_ID  ";
        private string STATUS = "@STATUS";
        private string TRANSPORT_EQUIPMENT_ID = "@TRANSPORT_EQUIPMENT_ID";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckBolIDUnique(string bolID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_BOL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckBolIDUnique(DataBase dataBase, DbTransaction tran, string bolID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_BOL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteBol(List<string> bolID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBol(DataBase dataBase, DbTransaction tran, List<string> bolID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < bolID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteBolByDispatchID(DataBase dataBase, DbTransaction tran, string dispatchID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = "DELETE FROM @_@BOL WHERE BOL_ID IN (SELECT DISTINCT BOL_ID FROM @_@BOL_DETAIL WHERE DISPATCH_ID = '" + dispatchID + "')";
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID));
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllBol(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllBol(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public BolInfo GetBolByID(string bolID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID) };
            BolInfo bolInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    bolInfo = new BolInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), reader["TRANSPORT_EQUIPMENT_ID"].ToString(), reader["DRIVER_ID"].ToString(), reader["STATUS"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["FREIGHT_SELF"].ToString() != "") ? decimal.Parse(reader["FREIGHT_SELF"].ToString()) : 0M, (reader["FREIGHT_DEALER"].ToString() != "") ? decimal.Parse(reader["FREIGHT_DEALER"].ToString()) : 0M, reader["PAID"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["DEPART_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPART_DATE"].ToString()) : new DateTime());
                }
            }
            return bolInfo;
        }

        public BolInfo GetBolByID(DataBase dataBase, DbTransaction tran, string bolID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID) + " WHERE BOL_ID = @BOL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BOL_ID", bolID) };
            BolInfo bolInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    bolInfo = new BolInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BOL_ID"].ToString(), reader["TRANSPORT_EQUIPMENT_ID"].ToString(), reader["DRIVER_ID"].ToString(), reader["STATUS"].ToString(), (reader["GROSS_WEIGHT"].ToString() != "") ? decimal.Parse(reader["GROSS_WEIGHT"].ToString()) : 0M, (reader["NET_WEIGHT"].ToString() != "") ? decimal.Parse(reader["NET_WEIGHT"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["FREIGHT_SELF"].ToString() != "") ? decimal.Parse(reader["FREIGHT_SELF"].ToString()) : 0M, (reader["FREIGHT_DEALER"].ToString() != "") ? decimal.Parse(reader["FREIGHT_DEALER"].ToString()) : 0M, reader["PAID"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["DEPART_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPART_DATE"].ToString()) : new DateTime());
                }
                reader.Close();
            }
            return bolInfo;
        }

        public DataSet GetBolByQueryList(List<string> sqlWhere, BolQueryEntity bolQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!bolQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, bolQuery.CurrentPage, bolQuery.PageSize, bolQuery.SortField, bolQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetBolByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, BolQueryEntity bolQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_BOL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, bolQuery.CurrentPage, bolQuery.PageSize, bolQuery.SortField, bolQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertBol(BolInfo bolInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Bol_Parameters(bolInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertBol(DataBase dataBase, DbTransaction tran, BolInfo bolInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Bol_Parameters(bolInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Bol_Parameters(BolInfo bolInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.BOL_ID, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSPORT_EQUIPMENT_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DRIVER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.STATUS, SqlDbType.VarChar, 30), new SqlParameter(this.GROSS_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.NET_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.FREIGHT_SELF, SqlDbType.Decimal, 13), new SqlParameter(this.FREIGHT_DEALER, SqlDbType.Decimal, 13), new SqlParameter(this.PAID, SqlDbType.VarChar, 1), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.DEPART_DATE, SqlDbType.DateTime)
             };
            if (!string.IsNullOrEmpty(bolInfo.WhID))
            {
                paramArray[0].Value = bolInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = bolInfo.BolID;
            paramArray[2].Value = bolInfo.TransportEquipmentID;
            paramArray[3].Value = bolInfo.DriverID;
            if (!string.IsNullOrEmpty(bolInfo.Status))
            {
                paramArray[4].Value = bolInfo.Status;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = bolInfo.GrossWeight;
            paramArray[6].Value = bolInfo.NetWeight;
            paramArray[7].Value = bolInfo.Cube;
            paramArray[8].Value = bolInfo.FreightSelf;
            paramArray[9].Value = bolInfo.FreightDealer;
            paramArray[10].Value = bolInfo.Paid;
            if (!string.IsNullOrEmpty(bolInfo.Udf1))
            {
                paramArray[11].Value = bolInfo.Udf1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf2))
            {
                paramArray[12].Value = bolInfo.Udf2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf3))
            {
                paramArray[13].Value = bolInfo.Udf3;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf4))
            {
                paramArray[14].Value = bolInfo.Udf4;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf5))
            {
                paramArray[15].Value = bolInfo.Udf5;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf6))
            {
                paramArray[0x10].Value = bolInfo.Udf6;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf7))
            {
                paramArray[0x11].Value = bolInfo.Udf7;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf8))
            {
                paramArray[0x12].Value = bolInfo.Udf8;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf9))
            {
                paramArray[0x13].Value = bolInfo.Udf9;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Udf10))
            {
                paramArray[20].Value = bolInfo.Udf10;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.Remark))
            {
                paramArray[0x15].Value = bolInfo.Remark;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(bolInfo.IsActive))
            {
                paramArray[0x16].Value = bolInfo.IsActive;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            paramArray[0x17].Value = bolInfo.CreatedBy;
            paramArray[0x18].Value = bolInfo.UpdatedBy;
            paramArray[0x19].Value = bolInfo.Qty;
            if (bolInfo.DepartDate == new DateTime())
            {
                paramArray[0x1a].Value = DBNull.Value;
                return paramArray;
            }
            paramArray[0x1a].Value = bolInfo.DepartDate;
            return paramArray;
        }

        public int UpdateBol(BolInfo bolInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Bol_Parameters(bolInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateBol(DataBase dataBase, DbTransaction tran, BolInfo bolInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    bolInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Bol_Parameters(bolInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BOL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateBolTotalByDispatchID(DataBase dataBase, DbTransaction tran, string dispatchID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = "UPDATE @_@BOL SET QTY = b.QTY, GROSS_WEIGHT = b.GROSS_WEIGHT, NET_WEIGHT = b.NET_WEIGHT, [CUBE] = b.[CUBE]  FROM @_@BOL bol INNER JOIN (SELECT BOL_ID, SUM(QTY) AS QTY, SUM(GROSS_WEIGHT) AS GROSS_WEIGHT, SUM(NET_WEIGHT) AS NET_WEIGHT, SUM([CUBE]) AS [CUBE] FROM @_@BOL_DETAIL WHERE DISPATCH_ID = '" + dispatchID + "' GROUP BY BOL_ID) b ON BOL.BOL_ID = b.BOL_ID";
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID));
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

